# 课堂作业：
# 1、练习Pymysq连接数据库，使用自己的数据库做连接
# 2、练习：
# 2.1、通过python连接pymysql数据库，封装类方法
# 2.1.1、随机插入100条数据到数据库表，包括姓名、年龄、家庭住址、身份证号码
# 2.1.2、查询全部的数据
# 2.1.3、删除指定一条数据
# 2.1.4、封装批量删除方法，可以删除多条
# 1.5、可以指定增加单独一条数据到表里
# 以上每个操作步骤要写日志信息到log.txt里
import pymysql,random
from faker import Faker
from datetime import datetime
import traceback
USER='root'
PASSWORD='123456'
HOST='8.148.229.201'
DATABASE='jiac'
faker = Faker(locale = 'zh_cn') # locale 设置国家为中国 zh_cn
class Student:
    def connect(self):
        self.db = pymysql.connect(user= USER,password=PASSWORD,host=HOST,database=DATABASE,cursorclass =
        pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()
        str1 = f"当前时间为：{datetime.now()},连接数据库！"
        with open("log.txt",'a',encoding='utf-8') as f:
            f.write(str1 + "\n")
    def close(self):
        self.cursor.close()
        self.db.close()
        str1 = f"当前时间为：{datetime.now()},关闭数据库！"
        with open("log.txt", 'a', encoding='utf-8') as f:
            f.write(str1 + "\n")
    def insert_100_row(self):
        content_list = []
        self.connect()
        for i in range(100):
            name = faker.name() # 名字
            age = random.randint(19,60) # 年龄
            address = faker.address() # 家庭住址
            ssn = faker.ssn(min_age=18,max_age=60)# 身份证号码
            # 表名字：student2   表字段：id、name、age、address、ssn
            str1 = f"insert into student2(name,age,address,ssn) values('{name}','{age}','{address}','{ssn}')"
            self.cursor.execute(str1) # 执行sql  使用cursor游标执行
            self.db.commit()
            str2 =  f"当前时间为：{datetime.now()}"+f"insert into student2(name,age,address,ssn) values('{name}','{age}','{address}','{ssn}')" + "\n"
            print(f"{str1}插入成功！")
            content_list.append(str2)
        with open("log.txt",'a',encoding='utf-8') as f:
            f.writelines(content_list)
        self.close()
        """
        用python原始类型：列表，把每一次收集到的数据加到列表里面
        最后使用writelines一次性写进日志里面，节省内存和cpu开销
        """
    def searchAll(self):
        self.connect()
        self.cursor.execute("select * from student2")
        result = self.cursor.fetchall()
        for index,value in enumerate(result):
            print(f"第{index+1}条的数据为：{value}")
        else:
            print("输出完毕!")
        with open("log.txt",'a',encoding='utf-8') as f:
            f.write(f"当前时间：{datetime.now()}，执行了查询操作！")
        self.close()
    def delete_row(self,name):
        self.connect()
        str1 = f"delete from student2 where name='{name}'"
        self.cursor.execute(str1)
        self.db.commit()
        with open('log.txt','a',encoding='utf-8') as f:
            f.write(f"当前时间：{datetime.now()}，删除：{str1}  成功！" + "\n")
        self.close()
    def delete_row_(self,*names):
        content_list = []
        self.connect()
        for name in names:
            str1 = f"delete from student2 where name='{name}'"
            self.cursor.execute(str1)
            self.db.commit()
            print(f"删除：{name}数据成功！")
            str2 = f"当前时间：{datetime.now()}"+f"delete from student2 where name={name}" +"删除成功！" +"\n"
            content_list.append(str2)
        else:
            print("全部删除完毕！")
        with open('log.txt','a',encoding='utf-8') as f:
            f.writelines(content_list)
        self.close()
        """
        用python原始类型：列表，把每一次收集到的数据加到列表里面
        最后使用writelines一次性写进日志里面，节省内存和cpu开销
        """
    def insert_one_row(self,name,age,address,ssn):
        self.connect()
        str1 = f"insert into student2(name,age,address,ssn) values('{name}','{age}','{address}','{ssn}')"
        self.cursor.execute(str1)
        self.db.commit()
        print(f"{str1}新增数据成功！")
        str2 =f"当前时间：{datetime.now()}" +f"insert into student2(name,age,address,ssn) values('{name}','{age}','{address}','{ssn}')" +"  新增成功！\n"
        with open('log.txt','a',encoding='utf-8') as f:
            f.write(str2)
        self.close()
# if __name__ == '__main__':
#     s1 = Student()
#     # s1.insert_100_row()
#     # s1.searchAll()
#     # s1.delete_row("易成")
#     # s1.delete_row_("张丽华","张倩") #
#     s1.insert_one_row("z张鑫",19,"沈阳市","22029788777892")


#
# 2、做一个学生点名系统，系统里支持：存入学生、更改学生、删除学生、点到学生姓名方法
#
# 存入学生（姓名、年龄、家庭地址、手机号）： 存入成功后，提示xxx存入成功
# 更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段
# 删除学生：删除的学生将不在展示
# 展示全部学生：可以看到全部的学生姓名
# 学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
#
# 以上内容，需要连接数据库，每次的数据都需要增、删、改、查去做
#  create table student2(id int primary key auto_increment,name varchar(30) not null,age int,address varchar(30) default "沈阳",phone varchar(50));
class StudentSystem:
    def connect(self):
        self.db = pymysql.connect(user=USER, password=PASSWORD, host=HOST, database=DATABASE, cursorclass=
        pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()
    def close(self):
        self.cursor.close()
        self.db.close()
    def insert_student(self,name,age,address,phone_number):
        # 第一步连接数据库
        self.connect()
        try:
            str1 = f"insert into student2(name,age,address,phone) values('{name}','{age}','{address}','{phone_number}')"
            self.cursor.execute(str1)
            self.db.commit()
        except:
            self.db.rollback()
            traceback.print_exc()
            print("数据新增失败！")
        else:
            print("数据新增成功！")
        self.close()
    def udpate_student(self,old_name,new_name,new_age,new_address,new_phone_number):
        # 第一步连接数据库
        self.connect()
        self.cursor.execute(f"select * from student2 where name='{old_name}'")
        result = self.cursor.fetchall()
        if result:
            self.cursor.execute(f"update student2 set name='{new_name}',age='{new_age}',address='{new_address}',phone='{new_phone_number}' where name='{old_name}'")
            self.db.commit()
            print(f"修改：{old_name}成功！")
        else:
            print(f"要修改的：{old_name}不存在！ 修改失败！")
        self.close()
    def delete_student(self,name):
        # 第一步连接数据库
        self.connect()
        try:
            self.cursor.execute(f"delete from student2 where name='{name}'")
            self.db.commit()
        except:
            self.db.rollback()
            traceback.print_exc()
            print("删除失败！")
        else:
            print(f"删除：{name}成功！")
        self.close()
    def show_all_student(self):
        # 第一步连接数据库
        self.connect()
        self.cursor.execute(f"select * from student2")
        result = self.cursor.fetchall()
        for index,value in enumerate(result):
            print(f"第{index+1}条的数据为：{value}")
        self.close()
    def dianming(self,name):
        # 第一步连接数据库
        self.connect()
        self.cursor.execute(f"select * from student2 where name='{name}'")
        result = self.cursor.fetchall()
        if result:
            print(f"{name}到！")
        else:
            print("学生不存在！点名失败！")
        self.close()
# s1 = StudentSystem()
# s1.insert_student("张三",19,'沈阳','1234135212')
# s1.udpate_student("张三","张鑫",19,"锦州",'123532423')
# s1.show_all_student()
# s1.dianming("张鑫1")

# 3、类作业，统计镰刀和斧头的数量，要使用类方法
"""
class ToolsCount:
    liandao = []
    futou = []
    def __init__(self,toolsType):
        if toolsType =='镰刀':
            self.liandao.append("镰刀")
        elif toolsType =='斧头':
            self.futou.append("斧头")
    @classmethod
    def count_print(self):
        print(f"镰刀的数量为：{len(self.liandao)}")
        print(f"斧头的数量为：{len(self.futou)}")
ToolsCount("镰刀")
ToolsCount("镰刀")
ToolsCount("镰刀")
ToolsCount("镰刀")
ToolsCount("镰刀")
ToolsCount("斧头")
ToolsCount("斧头")
ToolsCount("斧头")
ToolsCount("斧头")
ToolsCount("斧头")
ToolsCount("镰刀")
ToolsCount("镰刀")
ToolsCount.count_print()
"""



# 4、新建学生成绩管理系统，随机生成100个 学生信息插入到数据库：
# 学生信息组成：学号、学生姓名、英语成绩、数学成绩、语文成绩、班级号（班级号有：1809A，1809B，1810A，1810B）
#
# 成绩划分为：优秀，良好，中等，及格，不及格。良好比中等好。
#
# 优秀：91—100之间（包括90）
# 良好：81-90
# 中等：71-80
# 及格：60-70
# 不合格：分数低于60分。
#
# 成绩管理系统提供以下方法：
# 《1》 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
# 《2》 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
# 《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
# 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
#  create table student3(id int primary key auto_increment,name varchar(30) not null,english int,math int,chinese int,classno varchar(30));
class StudentSorce:
    def connect(self):
        self.db = pymysql.connect(user=USER, password=PASSWORD, host=HOST, database=DATABASE, cursorclass=
        pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()
    def close(self):
        self.cursor.close()
        self.db.close()
    def insert_100_row(self):
        self.connect()
        for i in range(100):
            name = faker.name() # 名字
            english = random.randint(0,100)
            math = random.randint(0,100)
            chinese = random.randint(0,100)
            classno = random.choice(['1809A','1809B','1810A','1810B'])
            str1 = f"insert into student3(name,english,math,chinese,classno) values('{name}','{english}','{math}','{chinese}','{classno}')"
            self.cursor.execute(str1)
            self.db.commit()
            print(f"{str1}      新增数据成功！")
        self.close()
    # 询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
    def EnglishStudentInfo(self):
        self.connect()
        str1 = f"select name,classno from student3 where english < 60" # 不及格
        str2 = f"select name,classno from student3 where english between 60 and 70" # 及格
        str3 = f"select name,classno from student3 where english between 71 and 80" # 中等
        str4 = f"select name,classno from student3 where english between 81 and 90" # 良好
        str5 = f"select name,classno from student3 where english between 91 and 100" # 优秀
        self.cursor.execute(str1)
        print(f"不及格的学生信息为：{self.cursor.fetchall()}")
        self.cursor.execute(str2)
        print(f"及格的学生信息为：{self.cursor.fetchall()}")
        self.cursor.execute(str3)
        print(f"中等的学生信息为：{self.cursor.fetchall()}")
        self.cursor.execute(str4)
        print(f"良好的学生信息为：{self.cursor.fetchall()}")
        self.cursor.execute(str5)
        print(f"优秀的学生信息为：{self.cursor.fetchall()}")
        self.close()

    # 《2》 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
    def MathClass(self):
        self.connect()
        str1 = f"select math,classno from student3 where classno='{'1809A'}' order by math desc"
        str2 = f"select math,classno from student3 where classno='{'1809B'}' order by math desc"
        str3 = f"select math,classno from student3 where classno='{'1810A'}' order by math desc"
        str4 = f"select math,classno from student3 where classno='{'1810B'}' order by math desc"
        self.cursor.execute(str1)
        A1809 = self.cursor.fetchall()
        self.cursor.execute(str2)
        B1809 = self.cursor.fetchall()
        self.cursor.execute(str3)
        A1810 = self.cursor.fetchall()
        self.cursor.execute(str4)
        B1810 = self.cursor.fetchall()
        print(f"1809A的数学成绩：{A1809}")
        print(f"1809B的数学成绩：{B1809}")
        print(f"1810A的数学成绩：{A1810}")
        print(f"1810B的数学成绩：{B1810}")
        self.close()
    # 《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
    def ClassCount(self):
        self.connect()
        str1 = f"select count(*) as '总人数',classno from student3 group by classno order by count(*) desc;"
        self.cursor.execute(str1)
        print(self.cursor.fetchall())
        self.close()
    # 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
    def OrderSort(self):
        self.connect()
        str1 = f"select math,classno from student3 where (english<60 and math<60) and (chinese<60 and classno='1809A')"
        str2 = f"select math,classno from student3 where (english<60 and math<60) and (chinese<60 and classno='1809B')"
        str3 = f"select math,classno from student3 where (english<60 and math<60) and (chinese<60 and classno='1810A')"
        str4 = f"select math,classno from student3 where (english<60 and math<60) and (chinese<60 and classno='1810B')"
        self.cursor.execute(str1)
        A1809 = self.cursor.fetchall()
        self.cursor.execute(str2)
        B1809 = self.cursor.fetchall()
        self.cursor.execute(str3)
        A1810 = self.cursor.fetchall()
        self.cursor.execute(str4)
        B1810 = self.cursor.fetchall()
        dict_obj = {"1809A":len(A1809),"1809B":len(B1809),'1810A':len(A1810),"1810B":len(B1810)}
        print(dict_obj)
        print(f"1809A不合格的学生信息：{A1809}")
        print(f"1809B不合格的学生信息：{B1809}")
        print(f"1810A不合格的学生信息：{A1810}")
        print(f"1810B不合格的学生信息：{B1810}")
        #{'1809A': 6, '1809B': 3, '1810A': 5, '1810B': 5}
        # {'1809A': 6, '1810B':5, '1810A': 5, '1809B': 3}
        items = list(dict_obj.items())  # [('1809A',6)...]
        # print(items)
        for i in range(len(items)):
            for j in range(len(items)-i-1):
                if items[j][1] < items[j+1][1]:
                    items[j+1],items[j] = items[j],items[j+1]
        # print(items)
        print(dict(items))
        self.close()


s1 = StudentSorce()
# s1.insert_100_row()
# s1.MathClass()
# s1.ClassCount()
s1.OrderSort()

# 5、定义一个图书管理系统类，图书可以新增、修改、删除，要求新增、修改、删除的图书，都要出现在数据库中
# 新增：图书名、作者名称、新建年月日
# 修改：图书名、作者名称、新建年月日
# 删除：图书删掉，且不在展示
# 查看：全量查询、精确查询、模糊查询需要用户传入形参来控制返回结果






